package persistence.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import model.Functie;
import model.Rol;
import model.User;
import persistence.dao.UserDAO;
import persistence.exceptions.RepositoryException;
import view.ApplicationView;

public class JdbcUserDAO implements UserDAO {

	private ApplicationView aw = null;
	private final ConnectionManager cm;

	public JdbcUserDAO() {
		cm = new ConnectionManager();
	}

	@Override
	public String getPosition(int personid) throws RepositoryException {
		String ret = "";
		Connection conn = null;
		try {
			conn = cm.getConnection();
			Statement st = conn.createStatement();

			ResultSet rs = st.executeQuery("SELECT * FROM person");
			// ResultSetMetaData rsMetaData = rs.getMetaData();
			Integer counter = 0, total = 0;
			while (rs.next()) {
				total++;
				if (rs.getInt(1) == personid)
					counter = total;

			}

			ret = counter.toString() + "/" + total.toString();
			st.close();

		} catch (SQLException ex) {
			System.out.println("SQL exception:" + ex);
			throw new RepositoryException("JDBC connection error!", ex);
		} finally {
			cm.closeConnection(conn);
		}

		return ret;
	}

	@Override
	public User getPerson(int id, String op) throws RepositoryException {
		Connection conn = cm.getConnection();

		Statement st;
		ResultSet rs;
		PreparedStatement ps;
		try {
			st = conn.createStatement();
			if (id == 0) {
				rs = st.executeQuery("SELECT * FROM person ORDER BY idperson ASC;");
			} else {
				String queryString = "SELECT * FROM person WHERE idperson "
						+ op + " ? ";
				if (op == "<")
					queryString = "SELECT * FROM person WHERE idperson " + op
							+ " ? ORDER BY idperson DESC";
				if (op == ">")
					queryString = "SELECT * FROM person WHERE idperson " + op
							+ " ? ORDER BY idperson ASC";

				ps = conn.prepareStatement(queryString);
				ps.setInt(1, id);
				rs = ps.executeQuery();
			}
			if (rs.next()) {
				String tmp = rs.getString("functie");
				Functie fc = Functie.valueOf(tmp);
				tmp = rs.getString("rol");
				Rol rol = Rol.valueOf(tmp);
				User p = new User(rs.getInt("idperson"), rs.getString("nume"),
						rs.getString("prenume"), rs.getString("telefon"),
						rs.getString("mobil"), rs.getString("email"), fc,
						rs.getString("descriere"), rs.getString("username"),
						rs.getString("password"), rol);
				cm.closeConnection(conn);
				return p;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			throw new RepositoryException("JDBC connection error!", e);
		} finally {
			cm.closeConnection(conn);
		}

		return null;

	}

	@Override
	public int getMinMaxId(String op) throws RepositoryException {
		Connection conn = cm.getConnection();
		Statement st;
		ResultSet rs;
		int x = 0;

		try {
			st = conn.createStatement();
			if (op == "min") {
				rs = st.executeQuery("SELECT MIN(idperson) as idperson FROM person");
			} else {
				rs = st.executeQuery("SELECT MAX(idperson) as idperson FROM person");
			}
			x = 0;
			if (rs.next()) {
				x = rs.getInt("idperson");
			}

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RepositoryException("JDBC connection error!", e);
		} finally {
			cm.closeConnection(conn);
		}

		return x;
	}

	@Override
	public int insertPerson(User p) throws RepositoryException {
		Connection conn = cm.getConnection();
		PreparedStatement ps;

		String queryString = "INSERT INTO person(nume,prenume,telefon,mobil,email,functie,descriere,username,password,rol) value(?,?,?,?,?,?,?,?,?,?)";
		try {
			ps = conn.prepareStatement(queryString);
			ps.setString(1, p.getNume());
			ps.setString(2, p.getPrenume());
			ps.setString(3, p.getTelefon());
			ps.setString(4, p.getMobil());
			ps.setString(5, p.getEmail());
			ps.setString(6, p.getFunctie().toString());
			ps.setString(7, p.getDescriere());
			ps.setString(8, p.getUsername());
			ps.setString(9, p.getPassword());
			ps.setString(10, p.getRol().toString());
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RepositoryException("JDBC connection error!", e);
		} finally {
			cm.closeConnection(conn);
		}
		return getMinMaxId("max");
	}

	@Override
	public void updatePerson(User p) throws RepositoryException {
		Connection conn = cm.getConnection();
		PreparedStatement ps;

		String queryString = "UPDATE  person SET nume = ?, prenume = ?, telefon = ?, mobil = ?, email = ?, functie =?, descriere = ?, username = ?, password = ? ,rol = ? where idperson = ?";
		try {
			ps = conn.prepareStatement(queryString);
			ps.setString(1, p.getNume());
			ps.setString(2, p.getPrenume());
			ps.setString(3, p.getTelefon());
			ps.setString(4, p.getMobil());
			ps.setString(5, p.getEmail());
			ps.setString(6, p.getFunctie().toString());
			ps.setString(7, p.getDescriere());
			ps.setString(8, p.getUsername());
			ps.setString(9, p.getPassword());
			ps.setString(10, p.getRol().toString());
			ps.setInt(11, p.getIdperson());
			ps.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
			throw new RepositoryException("JDBC connection error!", e);
		} finally {
			cm.closeConnection(conn);
		}

	}

	@Override
	public void deletePerson(int id) throws RepositoryException {
		Connection conn = cm.getConnection();
		PreparedStatement ps;
		String updateString = "DELETE FROM person where idperson = ?";
		try {
			ps = conn.prepareStatement(updateString);
			ps.setInt(1, id);
			ps.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RepositoryException("JDBC connection error!", e);
		} finally {
			cm.closeConnection(conn);
		}
	}

	@Override
	public User[] searchUser(String user) throws RepositoryException {
		Connection conn = cm.getConnection();
		PreparedStatement ps;
		ResultSet rs;
		User myPersons[] = new User[100];

		try {

			String searchString = "SELECT * FROM person where nume like ? or prenume like ?";
			ps = conn.prepareStatement(searchString);
			ps.setString(1, '%' + user + '%');
			ps.setString(2, '%' + user + '%');
			rs = ps.executeQuery();
			int i = 0;
			while (rs.next()) {
				String tmp = rs.getString("functie");
				Functie fc = Functie.valueOf(tmp);
				tmp = rs.getString("rol");
				Rol rol = Rol.valueOf(tmp);
				User p = new User(rs.getInt("idperson"), rs.getString("nume"),
						rs.getString("prenume"), rs.getString("telefon"),
						rs.getString("mobil"), rs.getString("email"), fc,
						rs.getString("descriere"), rs.getString("username"),
						rs.getString("password"), rol);
				myPersons[i++] = p;
			}
		} catch (SQLException e) {
			System.out.println(e);
			throw new RepositoryException("JDBC connection error!", e);
		} finally {
			cm.closeConnection(conn);
		}

		cm.closeConnection(conn);

		return myPersons;
	}

}
